/* 
Set directory 
*/

    global root ENTER DIRECTORY

	global hh06 	$root\Data\ASER\ASER 2006 Household Data
	global hh07 	$root\Data\ASER\ASER 2007 Household Data
	global hh08  	$root\Data\ASER\ASER 2008 Household Data
	global hh09 	$root\Data\ASER\ASER 2009 Household Data
	global hh10 	$root\Data\ASER\ASER 2010 Household Data
	global hh11 	$root\Data\ASER\ASER 2011 Household Data
	global hh12 	$root\Data\ASER\ASER 2012 Household Data
	global hh13		$root\Data\newASERData\ASER2013Household
	global hh14		$root\Data\newASERData\ASER2014Household
	
	global gis		$root\Data\ASER\Districts_Clean
	
	global data 	$root\Dta\ASER\

	**2006**
	
use "$hh06\ASER 2006_Household.dta", clear
*Reading
foreach var of varlist read_story read_para read_word read_letter read_nothing {
replace `var' = 0 if `var' == .
}

replace read_para = 1 if read_story == 1
replace read_word = 1 if read_para == 1
replace read_letter = 1 if read_word == 1


egen read = rowtotal(read_story read_para read_word read_letter)

replace read = . if read_nothing == 0 & read == 0

*Math
foreach var of varlist math_div math_sub math_numrec math_nothing {
replace `var' = 0 if `var' == .
}

replace math_sub = 1 if math_div == 1
replace math_numrec = 1 if math_sub == 1


egen math = rowtotal(math_div math_sub math_numrec)

replace math = . if math_nothing == 0 & math == 0

*keep if math != . & read != .

gen year = 2006

sort household_id

egen hhid = group(household_id)

keep state_code state_name district_code district_name village_code totalmember childslno childage sex year hhid ///
age_3to5schgoing age_3to5schnotgoing age_5to16govt age_5to16pvt age_5to16madarsa age_5to16egs age_5to16class age_5to16oosnever ///
age_5to16oosdropout age_5to16oosclass language_teach mother_age mother_schoolingattended mother_schoolingnotattended ///
mother_schoolingclass mother_testcanread mother_testcannotread mother_nottested read math


**Cleaning Other Variables
bys hhid: gen childid = _n 

gen age3to5bal = .
replace age3to5bal = 1 if age_3to5schgoing == 1
replace age3to5bal = 0 if age_3to5schnotgoing == 1

gen age5to16sch = .
replace age5to16sch = 1 if age_5to16govt == 1
replace age5to16sch = 2 if age_5to16pvt == 1
replace age5to16sch = 3 if age_5to16madarsa == 1
replace age5to16sch = 4 if age_5to16egs == 1

rename age_5to16class age5to16class

rename age_5to16oosnever age5to16schnev

rename age_5to16oosdropout age5to16schdrop

rename age_5to16oosclass age5to16schdropstd

gen mothersch = .
replace mothersch = 1 if mother_schoolingattended == 1
replace mothersch = 0 if mother_schoolingnotattended == 1

rename mother_schoolingclass motherschstd

gen motherread = .
replace motherread = 1 if mother_testcanread == 1
replace motherread = 0 if mother_testcannotread == 1

rename mother_nottested mothernotest

replace sex = 0 if sex == 2

rename language_teach testlang

keep state_code state_name district_code district_name village_code totalmember childage sex year hhid childid age3to5bal age5to16sch ///
age5to16class age5to16schnev age5to16schdrop age5to16schdropstd testlang mother_age mothersch motherschstd motherread mothernotest read math

save "$data\aser_06", replace
/*
**Extrating District/State 
keep state_name district_name year

bys district_name: keep if _n == 1

save "$data\temp_06", replace
*/
	**2007** 
	
use "$hh07\hh_2007.dta", clear

*Reading
foreach var of varlist basiclearninglevelsreadstory basiclearninglevelsreadpara basiclearninglevelsreadword basiclearninglevelsreadletter basiclearninglevelsreadnothing {
replace `var' = 0 if `var' == .
}

replace basiclearninglevelsreadpara = 1 if basiclearninglevelsreadstory == 1
replace basiclearninglevelsreadword = 1 if basiclearninglevelsreadpara == 1
replace basiclearninglevelsreadletter = 1 if basiclearninglevelsreadword == 1


egen read = rowtotal(basiclearninglevelsreadstory basiclearninglevelsreadpara basiclearninglevelsreadword basiclearninglevelsreadletter )

replace read = . if basiclearninglevelsreadnothing == 0 & read == 0

*Math
foreach var of varlist mathleveldivision mathlevelsubtraction mathlevelnumrec10to99 mathlevelnumrec1to9 mathlevelnothing {
replace `var' = 0 if `var' == .
}

replace mathlevelsubtraction = 1 if mathleveldivision == 1
replace mathlevelnumrec10to99 = 1 if mathlevelsubtraction == 1
replace mathlevelnumrec1to9 = 1 if mathlevelnumrec10to99 == 1

egen math = rowtotal(mathleveldivision mathlevelsubtraction mathlevelnumrec10to99 mathlevelnumrec1to9)

replace math = . if mathlevelnothing == 0 & math == 0

*keep if math != . & read != .

gen year = 2007

sort household_id

egen hhid = group(household_id)

keep state_code state_name district_code district_name village_id totalmember child_slno child_age child_sex  year hhid read math ///
mothergonetoschoolyes mothergonetoschoolno motherstudieduptoclass motherage anganvadistatusage3to5schgoing  ///
anganvadistatusage3to5schnotgoin schoolstatusage5to16govt schoolstatusage5to16pvt schoolstatusage5to16madarsa schoolstatusage5to16oth ///
child5to16schoolingclassstd age5to16neverbeentoschool age5to16dropout ge5to16dropoutstd tutionyes tutionno languageinwhichchildtested

**Cleaning Other Variables
bys hhid: gen childid = _n 

gen age3to5bal = .
replace age3to5bal = 1 if anganvadistatusage3to5schgoing == 1
replace age3to5bal = 0 if anganvadistatusage3to5schnotgoin == 1

gen age5to16sch = .
replace age5to16sch = 1 if schoolstatusage5to16govt == 1
replace age5to16sch = 2 if schoolstatusage5to16pvt == 1
replace age5to16sch = 3 if schoolstatusage5to16madarsa == 1
replace age5to16sch = 4 if schoolstatusage5to16oth == 1

rename child5to16schoolingclassstd age5to16class

rename age5to16neverbeentoschool age5to16schnev

rename age5to16dropout age5to16schdrop

rename ge5to16dropoutstd age5to16schdropstd

gen mothersch = .
replace mothersch = 1 if mothergonetoschoolyes == 1
replace mothersch = 0 if mothergonetoschoolno == 1

rename motherstudieduptoclass motherschstd

rename languageinwhichchildtested testlang

gen tuition = .
replace tuition = 1 if tutionyes == 1
replace tuition = 0 if tutionno == 1

rename village_id village_code

rename child_age childage
rename child_sex sex

replace sex = 0 if sex == 2

rename motherage mother_age

keep state_code state_name district_code district_name village_code totalmember childage sex year hhid childid age3to5bal age5to16sch ///
age5to16class age5to16schnev age5to16schdrop age5to16schdropstd testlang mother_age mothersch motherschstd read math tuition


save "$data\aser_07", replace

/*
**Extrating District/State 
keep state_name district_name year

bys district_name: keep if _n == 1

save "$data\temp_07", replace
*/
	**2008** 
	
use "$hh08\hh_2008.dta", clear


*Reading
foreach var of varlist read_level_2 read_level_1 read_word read_letter read_nothing{
replace `var' = 0 if `var' == .
}

replace read_level_1 = 1 if read_level_2 == 1
replace read_word = 1 if read_level_1 == 1
replace read_letter = 1 if read_word == 1


egen read = rowtotal(read_level_2 read_level_1 read_word read_letter)

replace read = . if read_nothing == 0 & read == 0

*Math
foreach var of varlist math_division math_subtraction math_num_10_99 math_num_1_9 math_nothing {
replace `var' = 0 if `var' == .
}

replace math_subtraction = 1 if math_division == 1
replace math_num_10_99 = 1 if math_subtraction == 1
replace math_num_1_9 = 1 if math_num_10_99 == 1

egen math = rowtotal( math_division math_subtraction math_num_10_99 math_num_1_9)

replace math = . if math_nothing  == 0 & math == 0

*keep if math != . & read != .

gen year = 2008

sort hh_id

egen hhid = group(hh_id)

keep state_code district_code village_code state_name district_name total_member  year hhid read math ///
 hhtype_katcha hhtype_semi_katcha hhtype_pucca hh_electricity_conn_yes hh_electricity_conn_no hh_electricity_obs_yes  ///
 hh_electricity_obs_no hh_tv_yes hh_tv_no hh_phone_yes hh_phone_no hh_no_animal hh_goat_lamb hh_cows_buffalo hh_other_animals ///
 child_no child_age child_gender mother_gone_to_school_yes mother_gone_to_school_no mother_class mother_age preschool_yes ///
 preschool_no school_class school_govt school_private school_madarsa school_other oos_never_enr oos_dropout oos_dropout_class ///
 oos_dropout_year test_language vlg_electricity_conn vlg_pucca_road vlg_std_booth vlg_post_office vlg_ration_shop vlg_bank ///
 vlg_govt_primary_school vlg_govt_middle_school vlg_govt_secondary_school vlg_govt_private_school read math

 
 **Cleaning Other Variables
bys hhid: gen childid = _n 

gen age3to5bal = .
replace age3to5bal = 1 if preschool_yes == 1
replace age3to5bal = 0 if preschool_no == 1

gen age5to16sch = .
replace age5to16sch = 1 if school_govt == 1
replace age5to16sch = 2 if school_private == 1
replace age5to16sch = 3 if school_madarsa == 1
replace age5to16sch = 4 if school_other == 1

rename school_class age5to16class

rename oos_never_enr age5to16schnev

rename oos_dropout age5to16schdrop

rename oos_dropout_class age5to16schdropstd

rename oos_dropout_year age5to16schdropyr

gen mothersch = .
replace mothersch = 1 if mother_gone_to_school_yes == 1
replace mothersch = 0 if  mother_gone_to_school_no == 1

rename  mother_class motherschstd

rename total_member totalmember

rename test_language testlang

rename child_age childage
rename child_gender sex

replace sex = 0 if sex == 2


**HH Charactersitics
gen hh_elec = .
replace hh_elec = 1 if hh_electricity_conn_yes == 1
replace hh_elec = 0 if hh_electricity_conn_no == 1

gen hh_elec_obs = .
replace hh_elec_obs = 1 if hh_electricity_obs_yes == 1
replace hh_elec_obs = 0 if hh_electricity_obs_no == 1

gen hh_tv = . 
replace hh_tv = 1 if hh_tv_yes == 1
replace hh_tv = 0 if hh_tv_no == 1

gen hh_phone = . 
replace hh_phone = 1 if hh_phone_yes == 1
replace hh_phone = 0 if hh_phone_no == 1

keep state_code state_name district_code district_name village_code totalmember childage sex year hhid childid age3to5bal age5to16sch ///
age5to16class age5to16schnev age5to16schdrop age5to16schdropstd age5to16schdropyr testlang mother_age mothersch motherschstd read math ///
hhtype_katcha hhtype_semi_katcha hhtype_pucca hh_elec hh_elec_obs hh_tv hh_phone hh_no_animal hh_goat_lamb hh_cows_buffalo hh_other_animals ///
vlg_electricity_conn vlg_pucca_road vlg_std_booth vlg_post_office vlg_ration_shop vlg_bank vlg_govt_primary_school vlg_govt_middle_school ///
vlg_govt_secondary_school vlg_govt_private_school

 
save "$data\aser_08", replace
/*
**Extrating District/State 
keep state_name district_name year

bys district_name: keep if _n == 1

save "$data\temp_08", replace
*/
	**2009** 
	
use "$hh09\hh_2009.dta", clear

*Reading
foreach var of varlist read_level_2 read_level_1 read_word read_letter read_nothing{
replace `var' = 0 if `var' == .
}

replace read_level_1 = 1 if read_level_2 == 1
replace read_word = 1 if read_level_1 == 1
replace read_letter = 1 if read_word == 1


egen read = rowtotal(read_level_2 read_level_1 read_word read_letter)

replace read = . if read_nothing == 0 & read == 0

*Math
foreach var of varlist math_division math_subtraction math_num_10_99 math_num_1_9 math_nothing {
replace `var' = 0 if `var' == .
}

replace math_subtraction = 1 if math_division == 1
replace math_num_10_99 = 1 if math_subtraction == 1
replace math_num_1_9 = 1 if math_num_10_99 == 1

egen math = rowtotal( math_division math_subtraction math_num_10_99 math_num_1_9)

replace math = . if math_nothing  == 0 & math == 0

*keep if math != . & read != .

gen year = 2009

sort hh_id

egen hhid = group(hh_id)

keep state_code district_code village_code state_name district_name total_member year hhid read math ///
hhtype_katcha hhtype_semi_katcha hhtype_pucca hh_electricity_conn_yes hh_electricity_conn_no hh_electricity_today ///
hh_toilet hh_tv_yes hh_tv_no hh_mobile_yes hh_mobile_no hh_no_vehicle hh_cycle hh_motor_cycle hh_three_wheeler ///
hh_four_wheeler hh_tractor child_no child_age child_gender preschool_yes kindergarton_yes preschool_no ///
school_class school_govt school_private school_madarsa school_other preschool_all oos_never_enr oos_dropout ///
oos_dropout_class oos_dropout_year tuition test_language father_age father_gone_to_school father_class mother_age ///
mother_gone_to_school mother_class mother_read_level_1 vlg_pucca_road vlg_electricity vlg_post_office vlg_std_booth ///
vlg_bank vlg_ration_shop vlg_govt_primary_health_clinic vlg_private_health_clinic vlg_asha_worker vlg_govt_primary_school ///
vlg_govt_middle_school vlg_govt_secondary_school vlg_private_school vlg_anganwadi


 **Cleaning Other Variables
bys hhid: gen childid = _n 

gen age3to5bal = .
replace age3to5bal = 1 if preschool_yes == 1
replace age3to5bal = 1 if kindergarton_yes == 1
replace age3to5bal = 0 if preschool_no == 1

gen age5to16sch = .
replace age5to16sch = 1 if school_govt == 1
replace age5to16sch = 2 if school_private == 1
replace age5to16sch = 3 if school_madarsa == 1
replace age5to16sch = 4 if school_other == 1

rename school_class age5to16class

rename oos_never_enr age5to16schnev

rename oos_dropout age5to16schdrop

rename oos_dropout_class age5to16schdropstd

rename oos_dropout_year age5to16schdropyr

rename preschool_all everpreschool
replace everpreschool = 0 if everpreschool == 2 

gen mothersch = .
replace mothersch = 1 if mother_gone_to_school == 1
replace mothersch = 0 if mother_gone_to_school == 2

rename  mother_class motherschstd

gen fathersch = .
replace fathersch = 1 if father_gone_to_school == 1
replace fathersch = 0 if father_gone_to_school == 2

rename  father_class fatherschstd

rename total_member totalmember

rename test_language testlang

rename child_age childage
rename child_gender sex

replace sex = 0 if sex == 2

replace tuition = 0 if tuition == 2


**HH Charactersitics
gen hh_elec = .
replace hh_elec = 1 if hh_electricity_conn_yes == 1
replace hh_elec = 0 if hh_electricity_conn_no == 1

gen hh_elec_obs = .
replace hh_elec_obs = 1 if hh_electricity_today == 1
replace hh_elec_obs = 0 if hh_electricity_today == 2

gen hh_tv = . 
replace hh_tv = 1 if hh_tv_yes == 1
replace hh_tv = 0 if hh_tv_no == 1

gen hh_phone = . 
replace hh_phone = 1 if hh_mobile_yes == 1
replace hh_phone = 0 if hh_mobile_no == 1

foreach var of varlist hh_toilet hh_no_vehicle hh_cycle hh_motor_cycle hh_three_wheeler hh_four_wheeler hh_tractor{
replace `var' = 0 if `var' == 2
}

**Village Characteristics 
rename vlg_electricity vlg_electricity_conn


keep state_code state_name district_code district_name village_code totalmember childage sex year hhid childid age3to5bal age5to16sch everpreschool ///
age5to16class age5to16schnev age5to16schdrop age5to16schdropstd age5to16schdropyr testlang mother_age mothersch motherschstd fathersch fatherschstd father_age read math tuition ///
hhtype_katcha hhtype_semi_katcha hhtype_pucca hh_elec hh_elec_obs hh_tv hh_phone  ///
hh_toilet hh_no_vehicle hh_cycle hh_motor_cycle hh_three_wheeler hh_four_wheeler hh_tractor ///
vlg_electricity_conn vlg_pucca_road vlg_std_booth vlg_post_office vlg_ration_shop vlg_bank vlg_govt_primary_school vlg_govt_middle_school ///
vlg_govt_secondary_school vlg_private_school vlg_govt_primary_health_clinic vlg_private_health_clinic vlg_asha_worker  vlg_anganwadi



save "$data\aser_09", replace
/*
**Extrating District/State 
keep state_name district_name year

bys district_name: keep if _n == 1

save "$data\temp_09", replace
*/
	**2010** 
	
use "$hh10\hh2010.dta", clear

*Reading
foreach var of varlist read_level_2 read_level_1 read_word read_letter read_nothing{
replace `var' = 0 if `var' == .
}

replace read_level_1 = 1 if read_level_2 == 1
replace read_word = 1 if read_level_1 == 1
replace read_letter = 1 if read_word == 1


egen read = rowtotal(read_level_2 read_level_1 read_word read_letter)

replace read = . if read_nothing == 0 & read == 0

*Math
foreach var of varlist math_division math_subtraction math_num_10_99 math_num_1_9 math_nothing {
replace `var' = 0 if `var' == .
}

replace math_subtraction = 1 if math_division == 1
replace math_num_10_99 = 1 if math_subtraction == 1
replace math_num_1_9 = 1 if math_num_10_99 == 1

egen math = rowtotal( math_division math_subtraction math_num_10_99 math_num_1_9)

replace math = . if math_nothing  == 0 & math == 0

*keep if math != . & read != .

gen year = 2010

sort hh_id

egen hhid = group(hh_id)

keep state_code state_name district_code district_name village_code year hhid math read total_member ///
hhtype_katcha hhtype_semi_katcha hhtype_pucca hh_electricity_conn_yes hh_electricity_conn_no ///
hh_electricity_today_yes hh_electricity_today_no hh_toilet_yes hh_toilet_no hh_tv_yes hh_tv_no ///
hh_cable_tv__yes hh_cable_tv__no hh_dvd_yes hh_dvd_no hh_mobile_yes hh_mobile_no hh_newspaper_yes ///
hh_newspaper_no hh_reading_material_yes hh_reading_material_no hh_computer_use_yes hh_computer_use_no ///
child_no child_age child_gender preschool_yes kindergarton_yes preschool_no school_class school_govt ///
school_private school_madarsa school_other oos_never_enr oos_dropout oos_dropout_class oos_dropout_year ///
tuition_yes tuition_no tuition_school_teacher_yes tuition_school_teacher_no test_language father_age ///
father_gone_to_school_yes father_gone_to_school_no father_class mother_age mother_gone_to_school_yes ///
mother_gone_to_school_no mother_class mother_mobile_test vlg_pucca_road vlg_electricity vlg_post_office ///
vlg_std_booth vlg_bank vlg_ration_shop vlg_govt_primary_health_clinic vlg_private_health_clinic vlg_internet_cafe ///
vlg_solar_energy vlg_govt_primary_school vlg_govt_middle_school vlg_govt_secondary_school vlg_private_school vlg_anganwadi 


 **Cleaning Other Variables
bys hhid: gen childid = _n 

gen age3to5bal = .
replace age3to5bal = 1 if preschool_yes == 1
replace age3to5bal = 1 if kindergarton_yes == 1
replace age3to5bal = 0 if preschool_no == 1

gen age5to16sch = .
replace age5to16sch = 1 if school_govt == 1
replace age5to16sch = 2 if school_private == 1
replace age5to16sch = 3 if school_madarsa == 1
replace age5to16sch = 4 if school_other == 1

rename school_class age5to16class

rename oos_never_enr age5to16schnev

rename oos_dropout age5to16schdrop

rename oos_dropout_class age5to16schdropstd

rename oos_dropout_year age5to16schdropyr

gen mothersch = .
replace mothersch = 1 if mother_gone_to_school_yes == 1
replace mothersch = 0 if mother_gone_to_school_no == 1

rename  mother_class motherschstd

gen fathersch = .
replace fathersch = 1 if father_gone_to_school_yes == 1
replace fathersch = 0 if  father_gone_to_school_no == 1

rename  father_class fatherschstd

rename total_member totalmember

rename test_language testlang

rename child_age childage
rename child_gender sex

replace sex = 0 if sex == 2

gen tuition = .
replace tuition = 1 if tuition_yes == 1
replace tuition = 0 if  tuition_no == 1

gen tuition_ownteach = .
replace tuition_ownteach = 1 if tuition_school_teacher_yes == 1
replace tuition_ownteach = 0 if tuition_school_teacher_no == 1


**HH Charactersitics
gen hh_elec = .
replace hh_elec = 1 if hh_electricity_conn_yes == 1
replace hh_elec = 0 if hh_electricity_conn_no == 1

gen hh_elec_obs = .
replace hh_elec_obs = 1 if hh_electricity_today_yes == 1
replace hh_elec_obs = 0 if hh_electricity_today_no == 1

gen hh_tv = . 
replace hh_tv = 1 if hh_tv_yes == 1
replace hh_tv = 0 if hh_tv_no == 1

gen hh_phone = . 
replace hh_phone = 1 if hh_mobile_yes == 1
replace hh_phone = 0 if hh_mobile_no == 1

gen hh_toilet = . 
replace hh_toilet = 1 if hh_toilet_yes == 1
replace hh_toilet = 0 if hh_toilet_no == 1

gen hh_cable = . 
replace hh_cable = 1 if hh_cable_tv__yes == 1
replace hh_cable = 0 if hh_cable_tv__no == 1

gen hh_dvd = . 
replace hh_dvd = 1 if hh_dvd_yes == 1
replace hh_dvd = 0 if hh_dvd_no == 1

gen hh_newspaper = . 
replace hh_newspaper = 1 if hh_newspaper_yes  == 1
replace hh_newspaper = 0 if hh_newspaper_no == 1

gen hh_reading_material = . 
replace hh_reading_material = 1 if hh_reading_material_yes == 1
replace hh_reading_material = 0 if hh_reading_material_no == 1

gen hh_computer_use = . 
replace hh_computer_use = 1 if hh_computer_use_yes  == 1
replace hh_computer_use = 0 if hh_computer_use_no == 1


**Village Characteristics 
rename vlg_electricity vlg_electricity_conn


keep state_code state_name district_code district_name village_code totalmember childage sex year hhid childid age3to5bal age5to16sch ///
age5to16class age5to16schnev age5to16schdrop age5to16schdropstd age5to16schdropyr testlang mother_age mothersch motherschstd fathersch fatherschstd father_age read math tuition ///
tuition_ownteach hhtype_katcha hhtype_semi_katcha hhtype_pucca hh_elec hh_elec_obs hh_tv hh_phone hh_toilet hh_cable hh_dvd hh_newspaper hh_reading_material hh_computer_use ///
vlg_electricity_conn vlg_pucca_road vlg_std_booth vlg_post_office vlg_ration_shop vlg_bank vlg_govt_primary_school vlg_govt_middle_school ///
vlg_govt_secondary_school vlg_private_school vlg_govt_primary_health_clinic vlg_private_health_clinic  vlg_anganwadi vlg_internet_cafe vlg_solar_energy vlg_govt_primary_school 


save "$data\aser_10", replace
/*
**Extrating District/State 
keep state_name district_name year

bys district_name: keep if _n == 1

save "$data\temp_10", replace
*/
	**2011** 
	
use "$hh11\household 2011.dta", clear

*Reading
foreach var of varlist read_level_2 read_level_1 read_word read_letter read_nothing{
replace `var' = 0 if `var' == .
}

replace read_level_1 = 1 if read_level_2 == 1
replace read_word = 1 if read_level_1 == 1
replace read_letter = 1 if read_word == 1


egen read = rowtotal(read_level_2 read_level_1 read_word read_letter)

replace read = . if read_nothing == 0 & read == 0

*Math
foreach var of varlist math_division math_subtraction math_num_10_99 math_num_1_9 math_nothing {
replace `var' = 0 if `var' == .
}

replace math_subtraction = 1 if math_division == 1
replace math_num_10_99 = 1 if math_subtraction == 1
replace math_num_1_9 = 1 if math_num_10_99 == 1

egen math = rowtotal( math_division math_subtraction math_num_10_99 math_num_1_9)

replace math = . if math_nothing  == 0 & math == 0

*keep if math != . & read != .

gen year = 2011

sort hh_id

egen hhid = group(hh_id)

keep state_code state_name district_code district_name village_code total_member hhid year ///
hh_type hh_electricity_conn hh_electricity_today hh_toilet hh_tv hh_cable_tv hh_mobile ///
hh_newspaper hh_reading_material hh_computer_use child_no child_age child_gender ///
school_class oos_never_enr oos_dropout oos_dropout_class oos_dropout_year tuition ///
tuition_school_teacher father_age father_gone_to_school_yes father_gone_to_school_no ///
father_class test_language school_govt school_private school_madarsa school_other preschool_yes ///
kindergarton_yes mother_age mother_gone_to_school_yes mother_gone_to_school_no mother_class ///
vlg_pucca_road vlg_electricity vlg_post_office vlg_std_booth vlg_bank vlg_ration_shop ///
vlg_govt_primary_health_clinic vlg_private_health_clinic vlg_internet_cafe vlg_solar_energy ///
vlg_govt_primary_school vlg_govt_middle_school vlg_govt_secondary_school vlg_private_school vlg_anganwadi math read ///


 **Cleaning Other Variables
bys hhid: gen childid = _n 

gen age3to5bal = .
replace age3to5bal = 1 if preschool_yes == 1
replace age3to5bal = 1 if kindergarton_yes == 1

gen age5to16sch = .
replace age5to16sch = 1 if school_govt == 1
replace age5to16sch = 2 if school_private == 1
replace age5to16sch = 3 if school_madarsa == 1
replace age5to16sch = 4 if school_other == 1

rename school_class age5to16class

rename oos_never_enr age5to16schnev

rename oos_dropout age5to16schdrop

rename oos_dropout_class age5to16schdropstd

rename oos_dropout_year age5to16schdropyr

gen mothersch = .
replace mothersch = 1 if mother_gone_to_school_yes == 1
replace mothersch = 0 if mother_gone_to_school_no == 1

rename  mother_class motherschstd

gen fathersch = .
replace fathersch = 1 if father_gone_to_school_yes == 1
replace fathersch = 0 if  father_gone_to_school_no == 1

rename  father_class fatherschstd

rename total_member totalmember

rename test_language testlang

rename child_age childage
rename child_gender sex

replace sex = 0 if sex == 2

replace tuition = 0 if  tuition == 2

rename tuition_school_teacher tuition_ownteach
replace tuition_ownteach = 0 if tuition_ownteach == 2


**HH Charactersitics
foreach var of varlist hh_electricity_conn hh_electricity_today hh_toilet hh_tv hh_cable_tv hh_mobile hh_newspaper hh_reading_material hh_computer_use{
replace `var' = 0 if `var' == 2
}

rename hh_electricity_conn hh_elec
rename hh_electricity_today hh_elec_obs
rename hh_mobile hh_phone
rename hh_cable_tv hh_cable

**Village Characteristics 
rename vlg_electricity vlg_electricity_conn


keep state_code state_name district_code district_name village_code totalmember childage sex year hhid childid age3to5bal age5to16sch ///
age5to16class age5to16schnev age5to16schdrop age5to16schdropstd age5to16schdropyr testlang mother_age mothersch motherschstd fathersch fatherschstd father_age read math tuition ///
tuition_ownteach hh_type hh_elec hh_elec_obs hh_tv hh_phone hh_toilet hh_cable hh_newspaper hh_reading_material hh_computer_use ///
vlg_electricity_conn vlg_pucca_road vlg_std_booth vlg_post_office vlg_ration_shop vlg_bank vlg_govt_primary_school vlg_govt_middle_school ///
vlg_govt_secondary_school vlg_private_school vlg_govt_primary_health_clinic vlg_private_health_clinic  vlg_anganwadi vlg_internet_cafe vlg_solar_energy vlg_govt_primary_school 


save "$data\aser_11", replace
/*
**Extrating District/State 
keep state_name district_name year

bys district_name: keep if _n == 1

save "$data\temp_11", replace
*/
	**2012** 
	
use "$hh12\ASERhousehold2012.dta", clear

*Reading
foreach var of varlist read_level_2 read_level_1 read_word read_letter read_nothing{
destring `var', replace
replace `var' = 0 if `var' == .
}

replace read_level_1 = 1 if read_level_2 == 1
replace read_word = 1 if read_level_1 == 1
replace read_letter = 1 if read_word == 1


egen read = rowtotal(read_level_2 read_level_1 read_word read_letter)

replace read = . if read_nothing == 0 & read == 0

*Math
foreach var of varlist math_division math_subtraction math_num_10_99 math_num_1_9 math_nothing {
destring `var', replace
replace `var' = 0 if `var' == .
}

replace math_subtraction = 1 if math_division == 1
replace math_num_10_99 = 1 if math_subtraction == 1
replace math_num_1_9 = 1 if math_num_10_99 == 1

egen math = rowtotal( math_division math_subtraction math_num_10_99 math_num_1_9)

replace math = . if math_nothing  == 0 & math == 0

*keep if math != . & read != .

gen year = 2012

sort hh_id

egen hhid = group(hh_id)

keep state_code state_name district_code district_name village_code hhid year math read total_member ///
hh_type hh_electricity_conn hh_electricity_today hh_toilet hh_tv hh_cable_tv hh_motor_vehicle ///
hh_newspaper hh_reading_material hh_computer_use hh_mobile child_no child_age child_gender school_class ///
oos_never_enr oos_dropout oos_dropout_class oos_dropout_year tuition test_language father_age ///
father_gone_to_school_yes father_gone_to_school_no father_class school_govt school_private ///
school_madarsa school_other preschool_yes kindergarton_yes preschool_no mother_age ///
mother_gone_to_school_yes mother_gone_to_school_no mother_class vlg_pucca_road vlg_electricity ///
vlg_post_office vlg_bank vlg_ration_shop vlg_govt_primary_health_clinic vlg_private_health_clinic ///
vlg_internet_cafe vlg_solar_energy vlg_govt_primary_school_1_5 vlg_govt_middle_school_1_8 ///
vlg_govt_secondary_school_1_10 vlg_govt_middle_school_6_8 vlg_private_school vlg_anganwadi

destring _all, replace

 **Cleaning Other Variables
bys hhid: gen childid = _n 

gen age3to5bal = .
replace age3to5bal = 1 if preschool_yes == 1
replace age3to5bal = 1 if kindergarton_yes == 1
replace age3to5bal = 0 if preschool_no == 1

gen age5to16sch = .
replace age5to16sch = 1 if school_govt == 1
replace age5to16sch = 2 if school_private == 1
replace age5to16sch = 3 if school_madarsa == 1
replace age5to16sch = 4 if school_other == 1

rename school_class age5to16class

rename oos_never_enr age5to16schnev

rename oos_dropout age5to16schdrop

rename oos_dropout_class age5to16schdropstd

rename oos_dropout_year age5to16schdropyr

gen mothersch = .
replace mothersch = 1 if mother_gone_to_school_yes == 1
replace mothersch = 0 if mother_gone_to_school_no == 1

rename  mother_class motherschstd

gen fathersch = .
replace fathersch = 1 if father_gone_to_school_yes == 1
replace fathersch = 0 if  father_gone_to_school_no == 1

rename  father_class fatherschstd

rename total_member totalmember

rename test_language testlang

rename child_age childage
rename child_gender sex

replace sex = 0 if sex == 2

replace tuition = 0 if  tuition == 2

**HH Charactersitics
foreach var of varlist hh_electricity_conn hh_electricity_today hh_toilet hh_motor_vehicle hh_tv hh_cable_tv hh_mobile hh_newspaper hh_reading_material hh_computer_use{
replace `var' = 0 if `var' == 2
}

rename hh_electricity_conn hh_elec
rename hh_electricity_today hh_elec_obs
rename hh_mobile hh_phone
rename hh_cable_tv hh_cable

**Village Characteristics 
foreach var of varlist vlg_pucca_road vlg_electricity vlg_post_office vlg_bank vlg_ration_shop vlg_govt_primary_health_clinic ///
vlg_private_health_clinic vlg_internet_cafe vlg_solar_energy vlg_govt_primary_school_1_5 vlg_govt_middle_school_1_8 ///
vlg_govt_secondary_school_1_10 vlg_govt_middle_school_6_8 vlg_private_school vlg_anganwadi {
replace `var' = "0" if `var' == `""No""'
replace `var' = "1" if `var' == `""Yes""'
destring `var', replace
}

rename vlg_electricity vlg_electricity_conn
rename vlg_govt_primary_school_1_5 vlg_govt_primary_school
rename vlg_govt_middle_school_1_8 vlg_govt_middle_school
rename vlg_govt_secondary_school_1_10 vlg_govt_secondary_school
rename vlg_govt_middle_school_6_8 vlg_govt_middle_school_2


keep state_code state_name district_code district_name village_code totalmember childage sex year hhid childid age3to5bal age5to16sch ///
age5to16class age5to16schnev age5to16schdrop age5to16schdropstd age5to16schdropyr testlang mother_age mothersch motherschstd fathersch fatherschstd father_age read math tuition ///
 hh_type hh_elec hh_elec_obs hh_tv hh_phone hh_toilet hh_motor_vehicle hh_cable hh_newspaper hh_reading_material hh_computer_use ///
vlg_electricity_conn vlg_pucca_road vlg_post_office vlg_ration_shop vlg_bank vlg_govt_primary_school vlg_govt_middle_school vlg_govt_middle_school_2 ///
vlg_govt_secondary_school vlg_private_school vlg_govt_primary_health_clinic vlg_private_health_clinic  vlg_anganwadi vlg_internet_cafe vlg_solar_energy vlg_govt_primary_school 

save "$data\aser_12", replace



			**2013** 
			
*filefilter "$hh13\Hh_clean_pub.csv" "$hh13\Hh_clean_pub_ff.csv", from(\Q) to()
			
insheet using "$hh13\Hh_clean_pub_ff.csv", clear

save "$hh13\household 13.dta", replace

use "$hh13\household 13.dta", clear

*Reading and Math

gen read = read_code
gen math = math_code

foreach var of varlist read math{
replace `var' = 0 if `var' == 1
replace `var' = 1 if `var' == 2
replace `var' = 2 if `var' == 3
replace `var' = 3 if `var' == 4
replace `var' = 4 if `var' == 5
}



*keep if math != . & read != .

gen year = 2013

sort hh_id

egen hhid = group(hh_id)

keep state_code state_name district_code district_name village_code hhid year math read total_member ///
hh_type hh_electricity_conn hh_electricity_today hh_toilet hh_tv hh_cable_tv hh_motor_vehicle ///
hh_newspaper hh_reading_material hh_computer_use hh_mobile child_no child_age child_gender school_class ///
oos_never_enr oos_dropout oos_dropout_class oos_dropout_year tuition test_language father_age ///
father_gone_to_school_yes father_gone_to_school_no father_class school_govt school_private ///
school_madarsa school_other preschool_yes kindergarton_yes preschool_no mother_age ///
mother_gone_to_school_yes mother_gone_to_school_no mother_class vlg_pucca_road vlg_electricity ///
vlg_post_office vlg_bank vlg_ration_shop vlg_govt_primary_health_clinic vlg_private_health_clinic ///
vlg_internet_cafe vlg_solar_energy vlg_govt_primary_school_1_5 vlg_govt_middle_school_1_8 ///
vlg_govt_secondary_school_1_10 vlg_govt_middle_school_6_8 vlg_private_school vlg_anganwadi ///
hh_grad tuition_amount

destring _all, replace

 **Cleaning Other Variables
bys hhid: gen childid = _n 

gen age3to5bal = .
replace age3to5bal = 1 if preschool_yes == 1
replace age3to5bal = 1 if kindergarton_yes == 1
replace age3to5bal = 0 if preschool_no == 1

gen age5to16sch = .
replace age5to16sch = 1 if school_govt == 1
replace age5to16sch = 2 if school_private == 1
replace age5to16sch = 3 if school_madarsa == 1
replace age5to16sch = 4 if school_other == 1

rename school_class age5to16class

rename oos_never_enr age5to16schnev

rename oos_dropout age5to16schdrop

rename oos_dropout_class age5to16schdropstd

rename oos_dropout_year age5to16schdropyr

gen mothersch = .
replace mothersch = 1 if mother_gone_to_school_yes == 1
replace mothersch = 0 if mother_gone_to_school_no == 1

rename  mother_class motherschstd

gen fathersch = .
replace fathersch = 1 if father_gone_to_school_yes == 1
replace fathersch = 0 if  father_gone_to_school_no == 1

rename  father_class fatherschstd

rename total_member totalmember

rename test_language testlang

rename child_age childage
rename child_gender sex

replace sex = 0 if sex == 2

replace tuition = 0 if  tuition == 2

**HH Charactersitics
foreach var of varlist hh_electricity_conn hh_electricity_today hh_toilet hh_motor_vehicle hh_tv hh_cable_tv hh_mobile hh_newspaper hh_reading_material hh_computer_use{
replace `var' = 0 if `var' == 2
}

rename hh_electricity_conn hh_elec
rename hh_electricity_today hh_elec_obs
rename hh_mobile hh_phone
rename hh_cable_tv hh_cable

**Village Characteristics 
foreach var of varlist vlg_pucca_road vlg_electricity vlg_post_office vlg_bank vlg_ration_shop vlg_govt_primary_health_clinic ///
vlg_private_health_clinic vlg_internet_cafe vlg_solar_energy vlg_govt_primary_school_1_5 vlg_govt_middle_school_1_8 ///
vlg_govt_secondary_school_1_10 vlg_govt_middle_school_6_8 vlg_private_school vlg_anganwadi {
replace `var' = "0" if `var' == "No"
replace `var' = "1" if `var' == "Yes"
destring `var', replace
}

rename vlg_electricity vlg_electricity_conn
rename vlg_govt_primary_school_1_5 vlg_govt_primary_school
rename vlg_govt_middle_school_1_8 vlg_govt_middle_school
rename vlg_govt_secondary_school_1_10 vlg_govt_secondary_school
rename vlg_govt_middle_school_6_8 vlg_govt_middle_school_2


keep state_code state_name district_code district_name village_code totalmember childage sex year hhid childid age3to5bal age5to16sch ///
age5to16class age5to16schnev age5to16schdrop age5to16schdropstd age5to16schdropyr testlang mother_age mothersch motherschstd fathersch fatherschstd father_age read math tuition ///
 hh_type hh_elec hh_elec_obs hh_tv hh_phone hh_toilet hh_motor_vehicle hh_cable hh_newspaper hh_reading_material hh_computer_use ///
vlg_electricity_conn vlg_pucca_road vlg_post_office vlg_ration_shop vlg_bank vlg_govt_primary_school vlg_govt_middle_school vlg_govt_middle_school_2 ///
vlg_govt_secondary_school vlg_private_school vlg_govt_primary_health_clinic vlg_private_health_clinic  vlg_anganwadi vlg_internet_cafe vlg_solar_energy vlg_govt_primary_school 

save "$data\aser_13", replace


			**2014** 
					
insheet using "$hh14\ASER Household2014.csv", clear

save "$hh14\household 14.dta", replace

use "$hh14\household 14.dta", clear

*Reading and Math

gen read = read_code
gen math = math_code

foreach var of varlist read math{
replace `var' = 0 if `var' == 1
replace `var' = 1 if `var' == 2
replace `var' = 2 if `var' == 3
replace `var' = 3 if `var' == 4
replace `var' = 4 if `var' == 5
}



*keep if math != . & read != .

gen year = 2014

sort hh_id

egen hhid = group(hh_id)

keep state_code state_name district_code district_name village_code hhid year math read total_member ///
hh_type hh_electricity_conn hh_electricity_today hh_toilet hh_tv hh_cable_tv hh_motor_vehicle ///
hh_newspaper hh_reading_material hh_computer_use hh_mobile child_no child_age child_gender school_class ///
oos_never_enr oos_dropout oos_dropout_class oos_dropout_year tuition test_language father_age ///
father_class school_govt school_private ///
school_madarsa school_other preschool_yes kindergarton_yes preschool_no mother_age ///
mother_class vlg_pucca_road vlg_electricity ///
vlg_post_office vlg_bank vlg_ration_shop vlg_govt_primary_health_clinic vlg_private_health_clinic ///
vlg_internet_cafe vlg_solar_energy vlg_govt_primary_school_1_5 vlg_govt_middle_school_1_8 ///
vlg_govt_secondary_school_1_10 vlg_govt_middle_school_6_8 vlg_private_school vlg_anganwadi ///
hh_grad tuition_amount father_gone_to_school mother_gone_to_school

destring _all, replace

 **Cleaning Other Variables
bys hhid: gen childid = _n 

gen age3to5bal = .
replace age3to5bal = 1 if preschool_yes == 1
replace age3to5bal = 1 if kindergarton_yes == 1
replace age3to5bal = 0 if preschool_no == 1

gen age5to16sch = .
replace age5to16sch = 1 if school_govt == 1
replace age5to16sch = 2 if school_private == 1
replace age5to16sch = 3 if school_madarsa == 1
replace age5to16sch = 4 if school_other == 1

rename school_class age5to16class

rename oos_never_enr age5to16schnev

rename oos_dropout age5to16schdrop

rename oos_dropout_class age5to16schdropstd

rename oos_dropout_year age5to16schdropyr

gen mothersch = mother_gone_to_school
replace mothersch = 0 if mother_gone_to_school == 2

rename  mother_class motherschstd

gen fathersch = father_gone_to_school
replace fathersch = 0 if father_gone_to_school == 2

rename  father_class fatherschstd

rename total_member totalmember

rename test_language testlang

rename child_age childage
rename child_gender sex

replace sex = 0 if sex == 2

replace tuition = 0 if  tuition == 2

**HH Charactersitics
foreach var of varlist hh_electricity_conn hh_electricity_today hh_toilet hh_motor_vehicle hh_tv hh_cable_tv hh_mobile hh_newspaper hh_reading_material hh_computer_use{
replace `var' = 0 if `var' == 2
}

rename hh_electricity_conn hh_elec
rename hh_electricity_today hh_elec_obs
rename hh_mobile hh_phone
rename hh_cable_tv hh_cable

**Village Characteristics 
foreach var of varlist vlg_pucca_road vlg_electricity vlg_post_office vlg_bank vlg_ration_shop vlg_govt_primary_health_clinic ///
vlg_private_health_clinic vlg_internet_cafe vlg_solar_energy vlg_govt_primary_school_1_5 vlg_govt_middle_school_1_8 ///
vlg_govt_secondary_school_1_10 vlg_govt_middle_school_6_8 vlg_private_school vlg_anganwadi {
replace `var' = "0" if `var' == "No"
replace `var' = "1" if `var' == "Yes"
destring `var', replace
}

rename vlg_electricity vlg_electricity_conn
rename vlg_govt_primary_school_1_5 vlg_govt_primary_school
rename vlg_govt_middle_school_1_8 vlg_govt_middle_school
rename vlg_govt_secondary_school_1_10 vlg_govt_secondary_school
rename vlg_govt_middle_school_6_8 vlg_govt_middle_school_2


keep state_code state_name district_code district_name village_code totalmember childage sex year hhid childid age3to5bal age5to16sch ///
age5to16class age5to16schnev age5to16schdrop age5to16schdropstd age5to16schdropyr testlang mother_age mothersch motherschstd fathersch fatherschstd father_age read math tuition ///
 hh_type hh_elec hh_elec_obs hh_tv hh_phone hh_toilet hh_motor_vehicle hh_cable hh_newspaper hh_reading_material hh_computer_use ///
vlg_electricity_conn vlg_pucca_road vlg_post_office vlg_ration_shop vlg_bank vlg_govt_primary_school vlg_govt_middle_school vlg_govt_middle_school_2 ///
vlg_govt_secondary_school vlg_private_school vlg_govt_primary_health_clinic vlg_private_health_clinic  vlg_anganwadi vlg_internet_cafe vlg_solar_energy vlg_govt_primary_school 

save "$data\aser_14", replace



append using "$data\aser_13"
append using "$data\aser_12"
append using "$data\aser_11"
append using "$data\aser_10"
append using "$data\aser_09"
append using "$data\aser_08"
append using "$data\aser_07"
append using "$data\aser_06"

save "$data\aser_append", replace

use "$data\aser_append", clear

*Creating unique hh identifier*
rename hhid hhid_old
egen hhid = concat(hhid_old year)

destring hhid, replace

save "$data\aser_all_temp", replace


use "$data\aser_all_temp", clear

replace district_name = subinstr(district_name, char(34), "", .)
replace state_name = subinstr(state_name, char(34), "", .)
replace state_name = proper(state_name)

replace state_name = "Andhra Pradesh" if state_name == "Andhhra Pradesh"
replace state_name = "Arunachal Pradesh" if state_name == "Arunachal Pardesh"
replace state_name = "Dadra & Nagar Haveli" if state_name == "Dadar & Nagar Haveli"
replace state_name = "Gujarat" if state_name == "Gujrat"
replace state_name = "Maharashtra" if state_name == "Maharashtra"
replace state_name = "Orissa" if state_name == "Orrisa"
replace state_name = "Puducherry" if state_name == "Pondicherry"
replace state_name = "Tamil Nadu" if state_name == "Tamilnadu"
replace state_name = "Uttar Pradesh" if state_name == "Up"
replace state_name = "Uttaranchal" if state_name == "Uttranchal"

merge m:m state_name district_name using "$gis\temp_location_cleaned_formerge"


drop if gisdistrictname == "missing"

keep if _merge == 3
drop _merge


drop if childage == .
drop if childage < 5

**Test Scores
*Generating normalized test scores from raw scores
gen nmath = .
gen nread = .


foreach var of varlist math read {
foreach num of numlist 2006/2014 {
forvalues i = 5/16 {
sum `var' if year == `num' & childage == `i'
replace n`var' = (`var' - r(mean))/r(sd) if year == `num' & childage == `i'
}
}
}

*Year of Birth
gen yr_birth = year - childage

save "$data\aser_all", replace


